/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     23/03/2013 06:11:42 a.m.                     */
/*==============================================================*/


-- Type package declaration
create or replace package PDTypes  
as
    TYPE ref_cursor IS REF CURSOR;
end;
/

-- Integrity package declaration
create or replace package IntegrityPackage AS
 procedure InitNestLevel;
 function GetNestLevel return number;
 procedure NextNestLevel;
 procedure PreviousNestLevel;
 end IntegrityPackage;
/

-- Integrity package definition
create or replace package body IntegrityPackage AS
 NestLevel number;

-- Procedure to initialize the trigger nest level
 procedure InitNestLevel is
 begin
 NestLevel := 0;
 end;


-- Function to return the trigger nest level
 function GetNestLevel return number is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 return(NestLevel);
 end;

-- Procedure to increase the trigger nest level
 procedure NextNestLevel is
 begin
 if NestLevel is null then
     NestLevel := 0;
 end if;
 NestLevel := NestLevel + 1;
 end;

-- Procedure to decrease the trigger nest level
 procedure PreviousNestLevel is
 begin
 NestLevel := NestLevel - 1;
 end;

 end IntegrityPackage;
/


drop trigger COMPOUNDDELETETRIGGER_CATEGORI
/

drop trigger COMPOUNDUPDATETRIGGER_CATEGORI
/

drop trigger COMPOUNDINSERTTRIGGER_CATEGORI
/

drop trigger COMPOUNDDELETETRIGGER_DETALLE_
/

drop trigger COMPOUNDUPDATETRIGGER_DETALLE_
/

drop trigger COMPOUNDINSERTTRIGGER_DETALLE_
/

drop trigger COMPOUNDDELETETRIGGER_FACTURA
/

drop trigger COMPOUNDUPDATETRIGGER_FACTURA
/

drop trigger COMPOUNDINSERTTRIGGER_FACTURA
/

drop trigger COMPOUNDDELETETRIGGER_LIBRO
/

drop trigger COMPOUNDUPDATETRIGGER_LIBRO
/

drop trigger COMPOUNDINSERTTRIGGER_LIBRO
/

drop trigger COMPOUNDDELETETRIGGER_PERSONA
/

drop trigger COMPOUNDUPDATETRIGGER_PERSONA
/

drop trigger COMPOUNDINSERTTRIGGER_PERSONA
/

drop trigger COMPOUNDDELETETRIGGER_SUBCATEG
/

drop trigger COMPOUNDUPDATETRIGGER_SUBCATEG
/

drop trigger COMPOUNDINSERTTRIGGER_SUBCATEG
/

drop trigger COMPOUNDDELETETRIGGER_USUARIO
/

drop trigger COMPOUNDUPDATETRIGGER_USUARIO
/

drop trigger COMPOUNDINSERTTRIGGER_USUARIO
/

alter table AUTOR
   drop constraint FK_AUTOR_PERSONA_A_PERSONA
/

alter table CLIENTE
   drop constraint FK_CLIENTE_PERSONA_C_PERSONA
/

alter table DETALLE_FACTURA
   drop constraint FK_DETALLE__DETALLE_L_LIBRO
/

alter table DETALLE_FACTURA
   drop constraint FK_DETALLE__FACTURA_D_FACTURA
/

alter table FACTURA
   drop constraint FK_FACTURA_CLIENTE_F_CLIENTE
/

alter table LIBRO
   drop constraint FK_LIBRO_LIBRO_AUT_AUTOR
/

alter table LIBRO
   drop constraint FK_LIBRO_LIBRO_SUB_SUBCATEG
/

alter table SUBCATEGORIA
   drop constraint FK_SUBCATEG_CATEGORIA_CATEGORI
/

alter table USUARIO
   drop constraint FK_USUARIO_PERSONA_U_PERSONA
/

drop table AUTOR cascade constraints
/

drop table CATEGORIA cascade constraints
/

drop table CLIENTE cascade constraints
/

drop index FACTURA_DETALLE_FK
/

drop index DETALLE_LIBRO_FK
/

drop table DETALLE_FACTURA cascade constraints
/

drop index CLIENTE_FACTURA_FK
/

drop table FACTURA cascade constraints
/

drop index LIBRO_SUBCATEGORIA_FK
/

drop table LIBRO cascade constraints
/

drop table PERSONA cascade constraints
/

drop index CATEGORIA_SUBCATEGORIA_FK
/

drop table SUBCATEGORIA cascade constraints
/

drop index RELATIONSHIP_7_FK
/

drop table USUARIO cascade constraints
/

drop sequence SEQ_CATEGORIA
/

drop sequence SEQ_DETALLE_FACTURA
/

drop sequence SEQ_FACTURA
/

drop sequence SEQ_LIBRO
/

drop sequence SEQ_PERSONA
/

drop sequence SEQ_SUBCATEGORIA
/

drop sequence SEQ_USUARIO
/

create sequence SEQ_CATEGORIA
increment by 1
start with 1
nocycle
/

create sequence SEQ_DETALLE_FACTURA
increment by 1
start with 1
nocycle
/

create sequence SEQ_FACTURA
increment by 1
start with 1
nocycle
/

create sequence SEQ_LIBRO
increment by 1
start with 1
nocycle
/

create sequence SEQ_PERSONA
increment by 1
start with 1
nocycle
/

create sequence SEQ_SUBCATEGORIA
increment by 1
start with 1
nocycle
/

create sequence SEQ_USUARIO
increment by 1
start with 1
nocycle
/

/*==============================================================*/
/* Table: AUTOR                                                 */
/*==============================================================*/
create table AUTOR  (
   PER_ID               INTEGER                         not null,
   AUT_PROFESION        VARCHAR2(100)                   not null,
   constraint PK_AUTOR primary key (PER_ID)
)
/

/*==============================================================*/
/* Table: CATEGORIA                                             */
/*==============================================================*/
create table CATEGORIA  (
   CAT_ID               INTEGER                         not null,
   CAT_NOMBRE           VARCHAR2(100)                   not null,
   CAT_DESCRIPCION      VARCHAR2(100),
   constraint PK_CATEGORIA primary key (CAT_ID)
)
/

/*==============================================================*/
/* Table: CLIENTE                                               */
/*==============================================================*/
create table CLIENTE  (
   PER_ID               INTEGER                         not null,
   CLI_CALIFICACION     INTEGER                         not null,
   constraint PK_CLIENTE primary key (PER_ID)
)
/

/*==============================================================*/
/* Table: DETALLE_FACTURA                                       */
/*==============================================================*/
create table DETALLE_FACTURA  (
   DET_ID               INTEGER                         not null,
   LIB_ID               INTEGER                         not null,
   FAC_ID               INTEGER                         not null,
   DET_CANTIDAD         INTEGER                         not null,
   DET_VALORUNI         FLOAT                           not null,
   DET_VALORTOTAL       FLOAT                           not null,
   constraint PK_DETALLE_FACTURA primary key (DET_ID)
)
/

/*==============================================================*/
/* Index: DETALLE_LIBRO_FK                                      */
/*==============================================================*/
create index DETALLE_LIBRO_FK on DETALLE_FACTURA (
   LIB_ID ASC
)
/

/*==============================================================*/
/* Index: FACTURA_DETALLE_FK                                    */
/*==============================================================*/
create index FACTURA_DETALLE_FK on DETALLE_FACTURA (
   FAC_ID ASC
)
/

/*==============================================================*/
/* Table: FACTURA                                               */
/*==============================================================*/
create table FACTURA  (
   FAC_ID               INTEGER                         not null,
   PER_ID               INTEGER,
   FAC_FECHA            TIMESTAMP                       not null,
   FAC_TOTAL            FLOAT                           not null,
   constraint PK_FACTURA primary key (FAC_ID)
)
/

/*==============================================================*/
/* Index: CLIENTE_FACTURA_FK                                    */
/*==============================================================*/
create index CLIENTE_FACTURA_FK on FACTURA (
   PER_ID ASC
)
/

/*==============================================================*/
/* Table: LIBRO                                                 */
/*==============================================================*/
create table LIBRO  (
   LIB_ID               INTEGER                         not null,
   SUB_ID               INTEGER                         not null,
   PER_ID               INTEGER,
   LIB_ISBN             VARCHAR2(50)                    not null,
   LIB_TITULO           VARCHAR2(300)                   not null,
   LIB_EDITORIAL        VARCHAR2(200)                   not null,
   LIB_NUMPAGINAS       INTEGER                         not null,
   LIB_RESUMEN          VARCHAR2(1000)                  not null,
   LIB_CANTDISPONIBLE   INTEGER                         not null,
   LIB_PRECIO           FLOAT                           not null,
   LIB_URL              VARCHAR2(200),
   constraint PK_LIBRO primary key (LIB_ID)
)
/

/*==============================================================*/
/* Index: LIBRO_SUBCATEGORIA_FK                                 */
/*==============================================================*/
create index LIBRO_SUBCATEGORIA_FK on LIBRO (
   SUB_ID ASC
)
/

/*==============================================================*/
/* Table: PERSONA                                               */
/*==============================================================*/
create table PERSONA  (
   PER_ID               INTEGER                         not null,
   PER_NOMBRES          VARCHAR2(100)                   not null,
   PER_DIRECCION        VARCHAR2(100)                   not null,
   PER_TELEFONO         VARCHAR2(50)                    not null,
   PER_FECHANAC         TIMESTAMP                       not null,
   PER_TIPODOCUMENTO    INTEGER                         not null
      constraint CKC_PER_TIPODOCUMENTO_PERSONA check (PER_TIPODOCUMENTO in (1,2,3,4)),
   PER_NUMDOCUMENTO     VARCHAR2(20)                    not null,
   constraint PK_PERSONA primary key (PER_ID),
   constraint UK_NUMDOCUMENTO_PER unique (PER_NUMDOCUMENTO)
)
/

/*==============================================================*/
/* Table: SUBCATEGORIA                                          */
/*==============================================================*/
create table SUBCATEGORIA  (
   SUB_ID               INTEGER                         not null,
   CAT_ID               INTEGER                         not null,
   SUB_NOMBRE           VARCHAR2(100)                   not null,
   SUB_DESCRIPCION      VARCHAR2(100),
   constraint PK_SUBCATEGORIA primary key (SUB_ID)
)
/

/*==============================================================*/
/* Index: CATEGORIA_SUBCATEGORIA_FK                             */
/*==============================================================*/
create index CATEGORIA_SUBCATEGORIA_FK on SUBCATEGORIA (
   CAT_ID ASC
)
/

/*==============================================================*/
/* Table: USUARIO                                               */
/*==============================================================*/
create table USUARIO  (
   USU_ID               INTEGER                         not null,
   PER_ID               INTEGER                         not null,
   USU_LOGIN            VARCHAR2(20)                    not null,
   USU_PASSWORD         VARCHAR2(20)                    not null,
   USU_TIPO             INTEGER                         not null
      constraint CKC_USU_TIPO_USUARIO check (USU_TIPO in (1,2,3)),
   constraint PK_USUARIO primary key (USU_ID),
   constraint AK_UK_PER_USU_USUARIO unique (PER_ID, USU_TIPO),
   constraint UK_LOGIN_USU unique (USU_LOGIN)
)
/

/*==============================================================*/
/* Index: RELATIONSHIP_7_FK                                     */
/*==============================================================*/
create index RELATIONSHIP_7_FK on USUARIO (
   PER_ID ASC
)
/

alter table AUTOR
   add constraint FK_AUTOR_PERSONA_A_PERSONA foreign key (PER_ID)
      references PERSONA (PER_ID)
/

alter table CLIENTE
   add constraint FK_CLIENTE_PERSONA_C_PERSONA foreign key (PER_ID)
      references PERSONA (PER_ID)
/

alter table DETALLE_FACTURA
   add constraint FK_DETALLE__DETALLE_L_LIBRO foreign key (LIB_ID)
      references LIBRO (LIB_ID)
/

alter table DETALLE_FACTURA
   add constraint FK_DETALLE__FACTURA_D_FACTURA foreign key (FAC_ID)
      references FACTURA (FAC_ID)
/

alter table FACTURA
   add constraint FK_FACTURA_CLIENTE_F_CLIENTE foreign key (PER_ID)
      references CLIENTE (PER_ID)
/

alter table LIBRO
   add constraint FK_LIBRO_LIBRO_AUT_AUTOR foreign key (PER_ID)
      references AUTOR (PER_ID)
/

alter table LIBRO
   add constraint FK_LIBRO_LIBRO_SUB_SUBCATEG foreign key (SUB_ID)
      references SUBCATEGORIA (SUB_ID)
/

alter table SUBCATEGORIA
   add constraint FK_SUBCATEG_CATEGORIA_CATEGORI foreign key (CAT_ID)
      references CATEGORIA (CAT_ID)
/

alter table USUARIO
   add constraint FK_USUARIO_PERSONA_U_PERSONA foreign key (PER_ID)
      references PERSONA (PER_ID)
/


create trigger COMPOUNDDELETETRIGGER_CATEGORI before delete
on CATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_CATEGORI before update
of CAT_ID
on CATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_CATEGORI before insert
on CATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "CAT_ID" uses sequence SEQ_CATEGORIA
    select SEQ_CATEGORIA.NEXTVAL INTO :new.CAT_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_DETALLE_ before delete
on DETALLE_FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_DETALLE_ before update
of DET_ID,
   LIB_ID,
   FAC_ID
on DETALLE_FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_DETALLE_ before insert
on DETALLE_FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "DET_ID" uses sequence SEQ_DETALLE_FACTURA
    select SEQ_DETALLE_FACTURA.NEXTVAL INTO :new.DET_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_FACTURA before delete
on FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_FACTURA before update
of FAC_ID,
   PER_ID
on FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_FACTURA before insert
on FACTURA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "FAC_ID" uses sequence SEQ_FACTURA
    select SEQ_FACTURA.NEXTVAL INTO :new.FAC_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_LIBRO before delete
on LIBRO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_LIBRO before update
of LIB_ID,
   SUB_ID,
   PER_ID
on LIBRO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_LIBRO before insert
on LIBRO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "LIB_ID" uses sequence SEQ_LIBRO
    select SEQ_LIBRO.NEXTVAL INTO :new.LIB_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_PERSONA before delete
on PERSONA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_PERSONA before update
of PER_ID,
   PER_NUMDOCUMENTO
on PERSONA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_PERSONA before insert
on PERSONA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "PER_ID" uses sequence SEQ_PERSONA
    select SEQ_PERSONA.NEXTVAL INTO :new.PER_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_SUBCATEG before delete
on SUBCATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_SUBCATEG before update
of SUB_ID,
   CAT_ID
on SUBCATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_SUBCATEG before insert
on SUBCATEGORIA for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "SUB_ID" uses sequence SEQ_SUBCATEGORIA
    select SEQ_SUBCATEGORIA.NEXTVAL INTO :new.SUB_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDDELETETRIGGER_USUARIO before delete
on USUARIO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
null;
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDUPDATETRIGGER_USUARIO before update
of USU_ID,
   PER_ID,
   USU_LOGIN,
   USU_TIPO
on USUARIO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    seq NUMBER;

begin
    seq := IntegrityPackage.GetNestLevel;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/


create trigger COMPOUNDINSERTTRIGGER_USUARIO before insert
on USUARIO for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "USU_ID" uses sequence SEQ_USUARIO
    select SEQ_USUARIO.NEXTVAL INTO :new.USU_ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/
